package com.simple.space.util.excel;

import org.apache.commons.collections.CollectionUtils;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * 导出excel文件，将数据集合根据指定的表头、字段导出至指定流中
 */
public class ExportExcel {

	private int flushRow = 1000;  // 缓冲大小行数,暂未支持
	private int maxRow = 50000;  // sheet页最大行数 默认为5W行
	private String sheetName = "Sheet"; // sheet默认名称
	List<ExcelColumn> headers ;  // excle列头
	private static final String charset = "UTF-8";

	public ExportExcel(){}

	public ExportExcel(List<ExcelColumn> headers){
		this(headers,50000);
	}

	/**
	 * @param maxRow 每个sheet页最大行数
	 */
	public ExportExcel(List<ExcelColumn> headers, int maxRow){
		this(headers,"Sheet",maxRow);
	}

	/**
	 * @param maxRow 每个sheet页最大行数
	 */
	public ExportExcel(List<ExcelColumn> headers, String sheetName, int maxRow){
		this.headers = headers;
		this.maxRow = maxRow;
		this.sheetName = sheetName;
	}

	/**
	 * @param os 文件输出流
	 * @param list 导出的数据集合
	 * @throws IOException
	 */
	public void export(OutputStream os,List<?> list) throws IOException{
		export(os, list, charset);
	}
	/**
	 * @param os 文件输出流
	 * @param list 导出的数据集合
	 * @throws IOException
	 */
	public void export(OutputStream os,List<?> list,String charset) throws IOException{
		if(os == null || list == null){
			// 参数为空时，停止导出
			return;
		}
		if(headers == null){
			// 表头为空时，停止导出
			return;
		}
		// 创建excel文件
		StringBuffer sb = creatExcel();
		// 设置excel文件样式
		String style = createStyle();
		if(style != null && style.length() > 0){
			sb.append(style);
		}
		// 创建第一个sheet
		sb.append(createSheet(sheetName, headers.size(), maxRow+1));
		for (int i = 0; i < list.size(); i++) {
			if(i > 0 && i % maxRow == 0){
				// 循环创建sheet,结束上一个sheet
				sb.append(createSheetFoot());
				sb.append(createSheet(sheetName+(i/maxRow), headers.size(), maxRow+1));
			}
			sb.append(createRow(list.get(i),headers));
            sb.append("\n");
            os.write(sb.toString().getBytes(charset));
            sb.setLength(0);
		}
		// 结束最后一个sheet
		sb.append(createSheetFoot());
		// 创建excel结尾
		sb.append(creatExcelFoot());
		// 写入输入流中
		os.write(sb.toString().getBytes(Charset.forName(charset)));
		os.flush();
	}
	
	/**
	 * 创建列头
	 * @return
	 */
	public static List<ExcelColumn> getHeaders(String[] fields,
			String[] hearders, Integer[] widths) {
		List<ExcelColumn> columnList = null;
		if (hearders != null && fields != null && widths != null 
				&& hearders.length == fields.length) {
			int len = hearders.length;
			int widthLen = widths.length;
			columnList = new ArrayList<ExcelColumn>(len);
			int width = 100;
			for (int i = 0; i < len; i++) {
				ExcelColumn ec = new ExcelColumn();
				ec.setFieldName(fields[i]);
				ec.setName(hearders[i]);
				if (widthLen > i) {
					width = widths[i];
				}
				ec.setWidth(width);
		    	columnList.add(ec);
			}
		}
    	return columnList;
	}
	
	/**
	 * @param os 文件输出流
	 * @param list 导出的数据集合
	 * @throws IOException
	 */
	public void export(OutputStream os, List<?> list, String charset,
			List<String[]> fieldsList, List<String[]> headersList,
			List<String> sheetNameList) throws IOException {
		if (os == null) {
			// 参数为空时，停止导出
			return;
		}

		if (list == null || fieldsList == null || sheetNameList == null
				|| headersList == null
				|| fieldsList.size() != sheetNameList.size()
				|| fieldsList.size() != headersList.size()
				|| fieldsList.size() != list.size()) {
			return;
		}

		// 创建excel文件
		StringBuffer sb = creatExcel();
		// 设置excel文件样式
		String style = createStyle();
		if (style != null && style.length() > 0) {
			sb.append(style);
		}
		if (CollectionUtils.isNotEmpty(sheetNameList)) {
			int size = sheetNameList.size();
			for (int i = 0; i < size; i++) {
				Integer[] width = {};
				List<ExcelColumn> headers = getHeaders(fieldsList.get(i),
						headersList.get(i), width);
				this.headers = headers;
				// 创建第一个sheet
				sb.append(createSheet(sheetNameList.get(i), headers.size(),
						maxRow + 1));

				List dataList = (List) list.get(i);
				if (CollectionUtils.isNotEmpty(dataList)) {
					int dataSize = dataList.size();
					for (int j = 0; j < dataSize; j++) {
						sb.append(createRow(dataList.get(j), headers));
						sb.append("\n");
						os.write(sb.toString().getBytes(charset));
						sb.setLength(0);
					}
				}
				// 循环创建sheet,结束上一个sheet
				sb.append(createSheetFoot());
			}
		}
		// 创建excel结尾
		sb.append(creatExcelFoot());
		// 写入输入流中
		os.write(sb.toString().getBytes(Charset.forName(charset)));
		os.flush();
	}

	protected String createRow(Object obj,List<ExcelColumn> headers){
		StringBuffer sb = new StringBuffer();
		sb.append("<Row>");
		for (ExcelColumn excelColumn : headers) {
			sb.append("<Cell><Data ss:Type=\"String\">");
			String getMethod = "get"+excelColumn.getFieldName().substring(0,1).toUpperCase()+excelColumn.getFieldName().substring(1);
			String value = "";
			try {
				Method m = obj.getClass().getMethod(getMethod, new Class[]{});
				if(m != null){
					value = excelColumn.format(m.invoke(obj, new Object[]{}));
				}
			} catch (Exception e) {
				value = "Error:"+e.getMessage();
			}
			sb.append(value);
			sb.append("</Data></Cell>");
		}
		sb.append("</Row>");

		return sb.toString();
	}


	protected String createStyle(){
		StringBuffer sb = new StringBuffer();
		sb.append("<Styles>\n");

        /*设置列头样式*/
        sb.append("<Style ss:ID=\"header\" ss:Name=\"header\">\n");//ss:ID=“header”对应下面的Row ss:StyleID=“header”
        sb.append("<Interior ss:Color=\"#c4d79b\" ss:Pattern=\"Solid\"/>\n");// 设置背景颜色
        sb.append("<Font ss:FontName=\"??¨¬?\" x:CharSet=\"134\" ss:Size=\"14\"/>\n");//设置字体
        sb.append("</Style>\n");

        /*其它默认样式设置*/
        sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
        //sb.append("<Alignment ss:Vertical=\"Center\"/>\n");
        sb.append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>\n");// 左中右设置，一个是水平，一个是垂直
        sb.append("<Borders>\n");
        sb.append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Color=\"#666\" ss:Weight=\"1\"/>\n");//左边框设置
        sb.append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Color=\"#666\" ss:Weight=\"1\"/>\n");//右边框设置
        sb.append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Color=\"#666\" ss:Weight=\"1\"/>\n");//下边框设置
        sb.append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Color=\"#666\" ss:Weight=\"1\"/>\n");//上边框设置
        sb.append("</Borders>\n");
        sb.append("<Font ss:FontName=\"??¨¬?\" x:CharSet=\"134\" ss:Size=\"12\"/>\n");
        sb.append("<Interior/>\n");
        sb.append("<NumberFormat/>\n");
        sb.append("<Protection/>\n");
        sb.append("</Style>\n");
        sb.append("</Styles>\n");
        return sb.toString();
	}

	private StringBuffer creatExcel(){
		// 创建一个excel应用文件
        StringBuffer sb = new StringBuffer();
        sb.append("<?xml version=\"1.0\"?>");
        sb.append("\n");
        sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
        sb.append("\n");
        sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
        sb.append("\n");
        sb.append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
        sb.append("\n");
        sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
        sb.append("\n");
        sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
        sb.append("\n");
        sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
        sb.append("\n");
        return sb;
	}


	private String creatExcelFoot(){
		StringBuffer sb = new StringBuffer();
        sb.append("</Workbook>");
        sb.append("\n");
        return sb.toString();
	}

	private String createSheet(String sheetName,int headersLength,int rowCount){
		StringBuffer sb = new StringBuffer();
		sb.append("<Worksheet ss:Name=\"" + sheetName + "\">");
        sb.append("\n");
        sb.append("<Table ss:ExpandedColumnCount=\"" + headersLength
                + "\" ss:ExpandedRowCount=\""+rowCount+"\" x:FullColumns=\"1\" x:FullRows=\"1\">");
        sb.append("\n");
        // 设置列宽
        for (int i = 0; i < headers.size(); i++) {
        	int w = headers.get(i).getWidth();
        	if(w <= 0){
        		w = 100;
        	}
            sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\""+w+"\"/>");
        }
        // 设置自定义列头
        String defined = definedHeader();
        if(defined != null && defined.length() > 0){
        	sb.append(defined);
        }
        // 设置列头
        sb.append("<Row>");
        for (int i = 0; i < headers.size(); i++) {
            sb.append("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">" + headers.get(i).getName() + "</Data></Cell>");
        }
        sb.append("</Row>");
        return sb.toString();
	}

	/**
	 * 自定义表头
	 * @return 自定义表头字符串
	 */
	public String definedHeader(){
		return null;
	}

	private String createSheetFoot(){
		StringBuffer sb = new StringBuffer();
        sb.append("</Table>");
        sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
        sb.append("\n");
        sb.append("<ProtectObjects>False</ProtectObjects>");
        sb.append("\n");
        sb.append("<ProtectScenarios>False</ProtectScenarios>");
        sb.append("\n");
        sb.append("</WorksheetOptions>");
        sb.append("\n");
        sb.append("</Worksheet>");
        return sb.toString();
	}


	public List<ExcelColumn> getHeaders() {
		return headers;
	}

	public void setHeaders(List<ExcelColumn> headers) {
		this.headers = headers;
	}

	public int getMaxRow() {
		return maxRow;
	}

	public void setMaxRow(int maxRow) {
		this.maxRow = maxRow;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public int getFlushRow() {
		return flushRow;
	}

	public void setFlushRow(int flushRow) {
		this.flushRow = flushRow;
	}
}
